package com.clgk.wechatmall.common.sql;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import org.apache.commons.lang3.StringUtils;

import com.clgk.wechatmall.common.sql.annocation.Column;
import com.clgk.wechatmall.common.sql.annocation.ID;
import com.clgk.wechatmall.common.sql.annocation.Table;
import com.clgk.wechatmall.common.sql.annocation.Temporal;
import com.clgk.wechatmall.common.sql.annocation.Transient;
import com.clgk.wechatmall.common.util.DateUtils;

/**
 * sql拼接工具类
 * @author tcloud
 *
 */  
public class SqlUtils {
	
	private static String key = "serialVersionUID";
	/**
	 * 会根据传进来的javaBean封装成一个sql（有值才会拼接）
	 * @param javaBean 
	 * @return 如：UPDATE TABLE SET NAME=:name,ID=:id,PLATFORM=:platform WHERE ID=:id
	 */
	public static String fullUpdateForBeanSql(Object javaBean) {
		if(javaBean == null){
			return "";
		}
		Table table = javaBean.getClass().getAnnotation(Table.class);
		if(table == null){
			return "";
		}
		StringBuffer sql = new StringBuffer("UPDATE ").append(table.value()).append(" SET ");
		StringBuffer condition = new StringBuffer();
		try {
			Field[] fields = javaBean.getClass().getDeclaredFields();
			for(Field f : fields){
				f.setAccessible(true);
				Object obj = f.get(javaBean);
				if(obj == null || StringUtils.isBlank(obj.toString())){
					continue;
				}
				Transient tr = f.getAnnotation(Transient.class);
				if(tr != null){
					continue;
				}
				/**
				 * 属性名
				 */
				String name = f.getName();
				if(name.equals(key)){
					continue;
				}
				
				ID id = f.getAnnotation(ID.class);
				Column c = f.getAnnotation(Column.class);
				if(id != null){
					if(c == null){
						condition.append(" WHERE ").append(name).append("=:").append(name);
					}else{
						condition.append(" WHERE ").append(c.name()).append("=:").append(name);
					}
					continue;
				}
				Temporal te = f.getAnnotation(Temporal.class);
				if(c == null){
					if(te==null){
						sql.append(name).append("=:").append(name).append(",");
					}else{
						sql.append(name).append("='").append(formatDate(DateUtils.parseDate(obj.toString(),te.value().getValue()), te.value().getValue())).append("',");
					}
				}else{
					if(te == null){
						sql.append(c.name()).append("=:").append(name).append(",");
					}else{
						sql.append(c.name()).append("='").append(formatDate(DateUtils.parseDate(obj.toString(),te.value().getValue()), te.value().getValue())).append("',");
					}
				}
				
			}
			if(sql.length() > 0){
				sql = new StringBuffer(sql.substring(0,sql.lastIndexOf(",")));
			}
			if(condition.length() <= 0){
				return "";
			}
			sql.append(condition);
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
		return sql.toString();
	}
	
	/**
	 * 会根据传进来的javaBean封装成一个sql（有值才会拼接）
	 * @param javaBean 
	 * @param updateEmpty 更新空字段（默认不更新）,Temporal除外(有值才更新) 
	 * @return 如：UPDATE TABLE SET NAME=:name,ID=:id,PLATFORM=:platform WHERE ID=:id
	 */
	public static String fullUpdateForBeanSql(Object javaBean,boolean updateEmpty) {
		if(javaBean == null){
			return "";
		}
		Table table = javaBean.getClass().getAnnotation(Table.class);
		if(table == null){
			return "";
		}
		StringBuffer sql = new StringBuffer("UPDATE ").append(table.value()).append(" SET ");
		StringBuffer condition = new StringBuffer();
		try {
			Field[] fields = javaBean.getClass().getDeclaredFields();
			for(Field f : fields){
				f.setAccessible(true);
				Object obj = f.get(javaBean);
				if(!updateEmpty && (obj == null || StringUtils.isBlank(obj.toString()))){
					continue;
				}
				Transient tr = f.getAnnotation(Transient.class);
				if(tr != null){
					continue;
				}
				/**
				 * 属性名
				 */
				String name = f.getName();
				if(name.equals(key)){
					continue;
				}
				
				ID id = f.getAnnotation(ID.class);
				Column c = f.getAnnotation(Column.class);
				if(id != null){
					if(c == null){
						condition.append(" WHERE ").append(name).append("=:").append(name);
					}else{
						condition.append(" WHERE ").append(c.name()).append("=:").append(name);
					}
					continue;
				}
				Temporal te = f.getAnnotation(Temporal.class);
				if(c == null){
					if(te==null){
						sql.append(name).append("=:").append(name).append(",");
					}else if(obj != null){
						sql.append(name).append("='").append(formatDate(DateUtils.parseDate(obj.toString(),te.value().getValue()), te.value().getValue())).append("',");
					}
				}else{
					if(te == null){
						sql.append(c.name()).append("=:").append(name).append(",");
					}else if(obj != null){
						sql.append(c.name()).append("='").append(formatDate(DateUtils.parseDate(obj.toString(),te.value().getValue()), te.value().getValue())).append("',");
					}
				}
				
			}
			if(sql.length() > 0){
				sql = new StringBuffer(sql.substring(0,sql.lastIndexOf(",")));
			}
			if(condition.length() <= 0){
				return "";
			}
			sql.append(condition);
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
		return sql.toString();
	}
	/**
	 * 会根据传进来的javaBean封装成一个sql（有值才会拼接）
	 * @param javaBean 
	 * @return 如：INSERT INTO TABLE(XX,YY) VALUES(:xx,:yy)
	 */
	public static String fullInsertForBeanSql(Object javaBean) {
		if(javaBean == null){
			return "";
		}
		Table table = javaBean.getClass().getAnnotation(Table.class);
		if(table == null){
			return "";
		}
		String headSql = "INSERT INTO " + table.value();
		StringBuffer sql = new StringBuffer();
		StringBuffer valueSql = new StringBuffer();
		try {
			
			Field[] fields = javaBean.getClass().getDeclaredFields();
			for(Field f : fields){
				Transient tr = f.getAnnotation(Transient.class);
				if(tr != null){
					continue;
				}
				f.setAccessible(true);
				Object obj = f.get(javaBean);
				
				String name = f.getName();
				if(name.equals(key)){
					continue;
				}
				
				Column c = f.getAnnotation(Column.class);
				Temporal te = f.getAnnotation(Temporal.class);
				if(obj == null || StringUtils.isBlank(obj.toString())){
					if(te == null){
						continue;
					}
					if(c == null){
						sql.append(name).append(",");
					}else{
						sql.append(c.name()).append(",");
					}
					valueSql.append("'").append(getToday(te.value().getValue())).append("',");
				}else{
					if(c == null){
						sql.append(name).append(",");
					}else{
						sql.append(c.name()).append(",");
					}
					valueSql.append(":").append(name).append(",");
				}
				
			}
			if(sql.length() > 0){
				sql = new StringBuffer(sql.substring(0,sql.lastIndexOf(",")));
				valueSql = new StringBuffer(valueSql.substring(0,valueSql.lastIndexOf(",")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		sql.insert(0, " (").insert(0, headSql).append(") ").append(" VALUES(").append(valueSql.toString()).append(")");
		return sql.toString();
	}
	
	/**
	 * 会根据传进来的javaBean封装成一个sql（有值才会拼接）
	 * @param javaBean 
	 * @return 如：DELETE FROM  TABLE WHERE ID=:id
	 */
	public static String fullDelForBeanSql(Object javaBean){
		if(javaBean == null){
			return "";
		}
		Table table = javaBean.getClass().getAnnotation(Table.class);
		if(table == null){
			return "";
		}
		StringBuilder sql = new StringBuilder(" DELETE FROM ").append(table.value()).append(" WHERE ");
		Field[] fields = javaBean.getClass().getDeclaredFields();
		for(Field f : fields){
			ID id = f.getAnnotation(ID.class);
			if(id != null){
				Column c = f.getAnnotation(Column.class);
				if(c == null){
					sql.append(f.getName().toUpperCase()).append("=:").append(f.getName());
				}else{
					sql.append(c.name().toUpperCase()).append("=:").append(f.getName());
				}
				break;
			}
		}
		return sql.toString();
	}
	
	/**
	 * 单个表查询拼接SQL
	 * @param javaBean 
	 * @param params
	 * @return 如： NAME=? AND ID=? AND DESCRIPTION=? AND PLATFORM=? 
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static String fullSelectForSingleTable(Object javaBean,List params){
		if(javaBean == null){
			return "";
		}
		StringBuffer sql = new StringBuffer();
		try {
			Class cla = Class.forName(javaBean.getClass().getName());
			Method[] methods = cla.getDeclaredMethods();
			for(Method m : methods){
				String name = m.getName();
				if(m.getName().startsWith("set")){
					continue;
				}
				Object obj = m.invoke(javaBean);
				if(obj != null && StringUtils.isNotBlank(obj.toString())){
					String objName = name.toString().substring(3);
					sql.append(" ").append(objName.toUpperCase()).append("=? AND");
					params.add(obj);
				}
			}
			if(sql.length() > 0){
				sql = new StringBuffer(sql.substring(0,sql.lastIndexOf("AND")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
		return sql.toString();
	}
	
	/**
	 * 单个表查询拼接SQL
	 * @param javaBean 
	 * @return 如：SELECT id,user_name FROM TABLENAME WHERE 1=1  AND user_name=:name
	 */
	public static String fullSelectForSingleTable(Object javaBean){
		if(javaBean == null){
			return "";
		}
		Table table = javaBean.getClass().getAnnotation(Table.class);
		if(table == null){
			return "";
		}
		
		StringBuilder select = new StringBuilder("SELECT ");
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		try {
			
			Field[] fields = javaBean.getClass().getDeclaredFields();
			for(Field f : fields){
				Transient tr = f.getAnnotation(Transient.class);
				if(tr != null){
					continue;
				}
				f.setAccessible(true);
				Object obj = f.get(javaBean);
				
				String name = f.getName();
				if(name.equals(key)){
					continue;
				}
				
				Column c = f.getAnnotation(Column.class);
				if(c == null){
					select.append(name).append(",");
				}else{
					select.append(c.name()).append(",");
				}
				
				if(obj != null && StringUtils.isNotBlank(obj.toString())){
					if(c == null){
						where.append(" AND ").append(name);
					}else{
						where.append(" AND ").append(c.name());
					}
					where.append("=:").append(name);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return select.toString().substring(0,select.length()-1) + " FROM " + table.value() + where.toString();
	}
	
	
	public static String formatDate(Date date, String parrent) {
        if (date == null)
            return "";
        if (parrent == null || "".equals(parrent)) {
            return formatDate(date);
        } else {
            DateFormat sf = new SimpleDateFormat(parrent);
            return sf.format(date);
        }
    }
	
	public static String formatDate(Date date) {
        if (date == null)
            return "";
        DateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
        return sf.format(date);
    }
	
	public static String getToday(String pattern) {
        Calendar now = Calendar.getInstance(TimeZone.getDefault());
        DateFormat sdf = new SimpleDateFormat(pattern);
        sdf.setTimeZone(TimeZone.getDefault());
        return (sdf.format(now.getTime()));
    }
}
